import java.sql.*;
import java.util.List;

public class XMLtoDB {
    // database
    private static final String DB_URL = "jdbc:mysql://localhost:3306/MajorAssignmentTeam3"
            + "?useSSL=false&serverTimezone=Asia/Shanghai";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "DUANyuLONG0612";
    private static final String TABLE_NAME = "TstudentInfo";
    // XML FILE
    private static String XML_FILE = "MyStudentInfo.xml";
    public static void setXmlFilePath(String path) {
        XML_FILE = path;
    }
    // convert xml to db
    public void convertXmlToDb() {
        Connection conn = null;
        try {
            // read file
            XMLStudentManager.MyXMLReader reader = new XMLStudentManager.MyXMLReader(XML_FILE);
            List<Student> students = reader.readAllStudents();
            if(students.isEmpty()) {
                System.out.println("XML FILE has 0 student");
                return;
            }
            // connection
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            if(!tableExists(conn)){
                createTable(conn);
            }
            String sql = "INSERT INTO TstudentInfo (id, name, gender, major) VALUES (?, ?, ?, ?)";
            int insertedCount = 0;
            try(PreparedStatement pstmt = conn.prepareStatement(sql)) {
                for(Student student : students) {
                    try{
                        pstmt.setString(1, student.getId());
                        pstmt.setString(2, student.getName());
                        pstmt.setString(3, student.getGender());
                        pstmt.setString(4, student.getMajor());
                        pstmt.addBatch();
                        if(++insertedCount % 100 == 0) {
                            pstmt.executeBatch();
                        }
                    } catch (SQLException e) {
                        System.err.println("insert " + student.getId() + " error");
                    }
                }
                pstmt.executeBatch();
                System.out.println("Success: insert " + insertedCount + "/" + students.size() + " students data");
            } catch (SQLException e) {
                System.err.println("DataBase err: " + e.getMessage());
            }
        } catch (Exception e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        } finally {
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    System.err.println("Close DB err: " + e.getMessage());
                }
            }
        }
    }
    // check table exist
    private boolean tableExists(Connection conn) throws SQLException {
        DatabaseMetaData meta = conn.getMetaData();
        try(ResultSet rs = meta.getTables(null, null, XMLtoDB.TABLE_NAME, new String[]{"TABLE"})) {
            return rs.next();
        }
    }
    // create table
    private void createTable(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            String sql = "CREATE TABLE TstudentInfo (" +
                    "id VARCHAR(20) PRIMARY KEY, " +
                    "name VARCHAR(50) NOT NULL, " +
                    "gender VARCHAR(10) NOT NULL, " +
                    "major VARCHAR(50) NOT NULL)";
            stmt.executeUpdate(sql);
            System.out.println("TABLE TstudentInfo create success");
        }
    }
    // clear table
    private void clearTable(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            stmt.executeUpdate("TRUNCATE TABLE TstudentInfo");
            System.out.println("CLEAR TABLE TstudentInfo");
        }
    }
}
